// vi:si:et:sw=4:sts=4:ts=4
package cat.hex.trainoid;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.util.Log;

import com.google.android.maps.GeoPoint;

public class DbAdapter
{
	/* Table 1: User */
	public static final String KEY_NAME = "name";
	public static final String KEY_WEIGHT = "weight";
	public static final String KEY_HEIGHT = "height";
	public static final String KEY_BIRTHDAY = "birthday";
	public static final String KEY_TOTAL_DISTANCE = "total_distance";
	public static final String KEY_TOTAL_TIME = "total_time";

	/* Table 2: Track */
	public static final String KEY_TYPE = "type";
	public static final String KEY_BODY = "body";
	public static final String KEY_CREATED = "created";

	/* Table 3: GPSPosition */
	public static final String KEY_LATITUDE = "latitude";
	public static final String KEY_LONGITUDE = "longitude";
	public static final String KEY_TIMESTAMP = "timestamp";
	public static final String KEY_ALTITUDE = "altitude";
	public static final String KEY_SPEED = "speed";
	public static final String KEY_TRACKID = "track_id";

	public static final String KEY_ROWID = "_id";

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private static final String DATABASE_NAME = "trainoid";
	private static final String DATABASE_TABLE_1 = "user";
	private static final String DATABASE_TABLE_2 = "track";
	private static final String DATABASE_TABLE_3 = "gps";
	private static final int DATABASE_VERSION = 3;

	static final private String tag = "dbadapter"; // for debugging (Log)

	/**
	 * Database creation SQL statements
	 */
	private static final String DATABASE_CREATE_1 =
		"create table " + DATABASE_TABLE_1 +" (" +
		KEY_ROWID + " integer primary key autoincrement, " +
		KEY_NAME + " string, " +
		KEY_WEIGHT + " integer, " +
		KEY_HEIGHT + " integer, " +
		KEY_TOTAL_DISTANCE + " integer, " +
		KEY_TOTAL_TIME + " integer, " +
		KEY_BIRTHDAY + " sting);";

	private static final String DATABASE_CREATE_2 =
		"create table " + DATABASE_TABLE_2 +" (" +
		KEY_ROWID + " integer primary key autoincrement, " +
		KEY_NAME + " string, " +
		KEY_BODY + " string, " +
		KEY_TYPE + " string, " +
		KEY_CREATED + " long not null);";

	private static final String DATABASE_CREATE_3 =
		"create table " + DATABASE_TABLE_3 +" (" +
		KEY_ROWID + " integer primary key autoincrement, " +
		KEY_TRACKID + " integer not null, " +
		KEY_LATITUDE + " double not null, " +
		KEY_LONGITUDE + " double not null, " +
		KEY_TIMESTAMP + " long not null," +
		KEY_ALTITUDE + " double not null," +
		KEY_SPEED + " float not null);";

	private static final String FILL_TABLE_1 =
		"insert into " + DATABASE_TABLE_1 +" (" +
		KEY_NAME + ", " +
		KEY_WEIGHT + ", " +
		KEY_HEIGHT + ", " +
		KEY_TOTAL_DISTANCE + ", " +
		KEY_TOTAL_TIME + ", " +
		KEY_BIRTHDAY + ") values (" +
		"'Name'," +
		"75, " +
		"180, " +
		"0, " +
		"0, " +
		"'30/06/83');";

	private static final String FILL_TABLE_3 =
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '2.12001066666667', '41.1235009', '1289858402000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '2.12001066666667', '41.1235009', '1289858403000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '2.22000916666667', '41.1235009333333', '1289858404000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '41.0235011333333', '2.1200055', '1290290400000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '41.4068342', '2.13669083333333', '1290376800000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '41.40683365', '2.1533435', '1290376802000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '2.12001066666667', '41.1235009', '1289858402000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '2.12001066666667', '41.1235009', '1289858403000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '41.4068333333333', '2.13668816666667', '1290376801000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '2.22000916666667', '41.1235009333333', '1289858404000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '41.0235011333333', '2.1200055', '1290290400000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '41.4068342', '2.13669083333333', '1290376800000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '41.4068333333333', '2.13668816666667', '1290376801000', '0.0', '0.0');"+
		"insert into gps (track_id, longitude, latitude, timestamp, altitude, speed) values ('0', '41.40683365', '2.1533435', '1290376802000', '0.0', '0.0');";

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper
	{
		DatabaseHelper(Context context)
		{
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
        public void onCreate(SQLiteDatabase db)
        {
            db.execSQL(DATABASE_CREATE_1);
            db.execSQL(DATABASE_CREATE_2);
            db.execSQL(DATABASE_CREATE_3);

            Log.d(tag, "Database created");

            db.execSQL(FILL_TABLE_1);
            //db.execSQL(FILL_TABLE_3);

            Log.d(tag, "Fixtures added (DDBB)");
        }

		@Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            Log.w(tag, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");

            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_1);
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_2);
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_3);

            onCreate(db);
        }
	}

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 *
	 * @param ctx the Context within which to work
	 */
	public DbAdapter(Context ctx)
	{
		this.mCtx = ctx;
	}

	/**
	 * Open the database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 *
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException if the database could be neither opened or created
	 */
	public DbAdapter open() throws SQLException
	{
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();

		return this;
	}

	public DbAdapter openReadonly() throws SQLException
	{
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getReadableDatabase();

		return this;
	}

	public void close()
	{
		mDbHelper.close();
	}

	/**
	 * Create a new GPS position for track identified by trackId using the
	 * information provided from Location class. If the position is
	 * successfully created return the new rowId, otherwise return
	 * a -1 to indicate failure.
	 *
	 * @param Location class of the position
	 * @return rowId or -1 if failed
	 */
	public long createPosition(long trackId, Location location)
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_LATITUDE, location.getLatitude());
		initialValues.put(KEY_LONGITUDE, location.getLongitude());
		//initialValues.put(KEY_TIMESTAMP, location.getTime());
		initialValues.put(KEY_TIMESTAMP, System.currentTimeMillis());
		initialValues.put(KEY_ALTITUDE, location.getAltitude());
		initialValues.put(KEY_SPEED, location.getSpeed());
		initialValues.put(KEY_TRACKID, trackId);

		return mDb.insert(DATABASE_TABLE_3, null, initialValues);
	}

	/**
	 * Delete the position with the given rowId
	 *
	 * @param rowId id of position to delete
	 * @return true if deleted, false otherwise
	 */
	public boolean deletePosition(long rowId)
	{
		return mDb.delete(DATABASE_TABLE_3, KEY_ROWID + "=" + rowId, null) > 0;
	}

	/**
	 * Return a Cursor over the list of all gps positions in the database
	 *
	 * @return Cursor over all positions
	 */
	public Cursor fetchAllPositions(long trackId)
	{
		return mDb.query(DATABASE_TABLE_3, new String[] {KEY_ROWID, KEY_LATITUDE,
			KEY_LONGITUDE, KEY_ALTITUDE, KEY_SPEED, KEY_TIMESTAMP}, KEY_TRACKID + " = " + trackId, null, null, null, null);
	}

	/**
	 * Return a Cursor positioned at the position that matches the given rowId.
	 *
	 * @param rowId id of position to retrieve.
	 * @return Cursor positioned to matching position, if found.
	 * @throws SQLException if position could not be found/retrieved.
	 */
	public Cursor fetchPosition(long rowId) throws SQLException
	{
		Cursor mCursor =
			mDb.query(true, DATABASE_TABLE_3, new String[] {KEY_ROWID,
				KEY_LATITUDE, KEY_LONGITUDE, KEY_ALTITUDE, KEY_SPEED, KEY_TIMESTAMP},
				KEY_ROWID + "=" + rowId, null,
				null, null, null, null);

		if (mCursor != null)
		{
			mCursor.moveToFirst();
		}

		return mCursor;
	}

	/**
	 * Update the position using the details provided. The position to be updated is
	 * specified using the rowId.
	 *
	 * @param rowId id of note to update.
	 * @param location Location class with Geo-position information.
	 * @return true if the note was successfully updated, false otherwise.
	 */
	public boolean updatePosition(long rowId, Location location)
	{
		ContentValues args = new ContentValues();
		args.put(KEY_LATITUDE, location.getLatitude());
		args.put(KEY_LONGITUDE, location.getLongitude());
		args.put(KEY_ALTITUDE, location.getAltitude());
		args.put(KEY_SPEED, location.getSpeed());
		args.put(KEY_TIMESTAMP, location.getTime());

		return mDb.update(DATABASE_TABLE_3, args, KEY_ROWID + "=" + rowId, null) > 0;
	}

	public long createTrack()
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NAME, "New Track");
		initialValues.put(KEY_CREATED, System.currentTimeMillis());

		// missing date value

		return mDb.insert(DATABASE_TABLE_2, null, initialValues);
	}

	public long updateTrackName(long rowId, String name)
	{
		ContentValues args = new ContentValues();
		args.put(KEY_NAME, name);

		return mDb.update(DATABASE_TABLE_2, args, KEY_ROWID + "=" + rowId, null);
	}

	public boolean deleteTrack(long rowId)
	{
		boolean result = false;
		result = deleteTrackPositions(rowId);
		
		if (result)
			result = mDb.delete(DATABASE_TABLE_2, KEY_ROWID + "=" + rowId, null) > 0;
			
		return result;
	}
	
	/**
	 * Delete the track positions with the given rowId as TrackId
	 *
	 * @param rowId id of track positions to delete
	 * @return true if deleted, false otherwise
	 */
	public boolean deleteTrackPositions(long trackId)
	{
		return mDb.delete(DATABASE_TABLE_3, KEY_TRACKID + "=" + trackId, null) > 0;
	}

	/**
	 * Return a Cursor over the list of all tracks in the database
	 *
	 * @return Cursor over all tracks
	 */
	public Cursor fetchAllTracks()
	{
		return mDb.query(DATABASE_TABLE_2, new String[] {KEY_ROWID, KEY_NAME,
			KEY_BODY, KEY_TYPE, KEY_CREATED}, null, null, null, null, null);
	}

	/**
	 * Count the number of track's positions.
	 *
	 *
	 * @param trackId id of track to count.
	 * @return number of the track's positions.
	 */
	public int countTrackPositions(long trackId)
	{
		Cursor cCount = mDb.rawQuery("SELECT count(" + KEY_ROWID +") FROM " + DATABASE_TABLE_3 + " WHERE " + KEY_TRACKID + " = " + trackId, null);
		int count = 0;

		if (cCount.getCount() > 0)
		{
			cCount.moveToFirst();
			count = cCount.getInt(0);
		}
		cCount.close();

		//Log.d(tag, "resultat final dels punts del track = " + count);

		return count;
	}

	/**
	 * Constructs an ArrayList of GeoPoints
	 *
	 *
	 * @param trackId id of track get the points.
	 * @return ArrayList of track's GeoPoints.
	 */
	public ArrayList<GeoPoint> getTrackPath(long trackId)
	{
		ArrayList<GeoPoint> oneRoute = new ArrayList<GeoPoint>();
		Cursor c = fetchAllPositions(trackId);

		if (c.getCount() > 0)
		{
			for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext())
			{
				GeoPoint point = new GeoPoint((int)(c.getDouble(1)*1e6), (int)(c.getDouble(2)*1e6));
				oneRoute.add(point);

				/*
				   Log.w(tag, "* * * * * * * * * * *     Punt afegit a la ruta des GpsDbAdapter "
				   + point.getLatitudeE6()
				   + " "
				   + point.getLongitudeE6());
				   */
			}
		}

		c.close();
		return oneRoute;
	}

	/**
	 * Constructs an ArrayList of Location(s)
	 *
	 *
	 * @param trackId id of track get the points.
	 * @return ArrayList of track's Location(s).
	 */
	public ArrayList<Location> getTrackLocationPath(long trackId)
	{
		ArrayList<Location> oneRoute = new ArrayList<Location>();
		Cursor c = fetchAllPositions(trackId);

		if (c.getCount() > 0)
		{
			for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext())
			{
				Location point = new Location("");
				point.setLatitude(c.getDouble(1));
				point.setLongitude(c.getDouble(2));
				point.setTime(c.getLong(3));
				point.setAltitude(c.getDouble(4));
				point.setSpeed(c.getFloat(5));

				oneRoute.add(point);

				/*
				   Log.w(tag, "* * * * * * * * * * *     Punt afegit a la ruta des GpsDbAdapter "
				   + point.getLatitudeE6()
				   + " "
				   + point.getLongitudeE6());
				   */
			}
		}

		c.close();
		return oneRoute;
	}

	/**
	 * Get the user name as a String.
	 *
	 * @param userId is the unique ID of the user.
	 * @return String the name of the user.
	 */
	public String getUserName(int userId)
	{
		String userName = "";
		Cursor mCursor = mDb.query(DATABASE_TABLE_1, new String[] {KEY_NAME},
				KEY_ROWID + "=" + userId, null, null, null, null, null);

		if (mCursor != null)
		{
			mCursor.moveToFirst();
			userName = mCursor.getString(0);
		}

		return userName;
	}

	/**
	 * Get all the data about an user.
	 *
	 * @param userId the unique ID of the user.
	 * @return Cursor of user's data.
	 */
	public Cursor getUserData(long userId)
	{
		Cursor mCursor = mDb.query(DATABASE_TABLE_1, new String[] {KEY_NAME, KEY_WEIGHT, KEY_HEIGHT, KEY_TOTAL_DISTANCE, KEY_TOTAL_TIME, KEY_BIRTHDAY},
				KEY_ROWID + "=" + userId, null, null, null, null, null);

		if (mCursor != null)
			mCursor.moveToFirst();

		return mCursor;
	}

	/**
	 * Update the total user distance using the details provided. The User to be updated is
	 * specified using the rowId.
	 *
	 * @param rowId id of note to update.
	 * @param distance distance to set as new total user distance.
	 * @return true if the note was successfully updated, false otherwise.
	 */
	public boolean updateUserDistance(long rowId, float distance)
	{
		ContentValues args = new ContentValues();
		args.put(KEY_TOTAL_DISTANCE, distance);

		return mDb.update(DATABASE_TABLE_1, args, KEY_ROWID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Update the total user time using the details provided. The User to be updated is
	 * specified using the rowId.
	 *
	 * @param rowId id of note to update.
	 * @param time time to set as new total user time.
	 * @return true if the note was successfully updated, false otherwise.
	 */
	public boolean updateUserTime(long rowId, float time)
	{
		ContentValues args = new ContentValues();
		args.put(KEY_TOTAL_TIME, time);

		return mDb.update(DATABASE_TABLE_1, args, KEY_ROWID + "=" + rowId, null) > 0;
	}

	/**
	 * Start a {@link SQLiteDatabase} transaction.
	 */
	public void beginTransaction()
	{
		mDb.beginTransaction();
	}

	/**
	 * End a {@link SQLiteDatabase} transaction.
	 */
	public void endTransaction()
	{
		mDb.endTransaction();
	}

	/**
	 * End a {@link SQLiteDatabase} transaction.
	 */
	public void setTransactionSuccessful()
	{
		mDb.setTransactionSuccessful();
	}
}
